﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using System.IO;
using System.Configuration;
using System.Data.Linq;

namespace GoldenTax
{
    public partial class FormExport : Form
    {
        public FormExport()
        {
            InitializeComponent();
        }
        
        private string conn = ConfigurationManager.ConnectionStrings["KDDBConnection"].ToString();
        private IList<K3Invoice> invoices= new List<K3Invoice>();
        
		void ButtonQueryClick(object sender, EventArgs e)
		{
			using (var dbContext = new DataContext(conn))
            {
                DateTime beginDate, endDate;
                beginDate = dateTimePickerBegin.Value;
                endDate = dateTimePickerEnd.Value;

                string qString = "SELECT * FROM VW_T_Invoice WHERE 1=1";


                if (checkBoxDate.Checked)
                {
                    qString += string.Format(" AND FDate BETWEEN '{0}' AND '{1}'", beginDate.ToString("yyyy-MM-dd"),
                        endDate.ToString("yyyy-MM-dd"));
                }

                if (comboBoxFiltField.SelectedItem!=null&& !string.IsNullOrEmpty(comboBoxFiltField.SelectedItem.ToString()))
                {
                    switch (comboBoxFiltField.SelectedItem.ToString())
                    {
                        case "无":
                            break;
                        case "发票编号":
                            qString += string.Format(" AND FInvoiceNumber LIKE '%{0}%'",textBoxCondition.Text);
                            break;
                        case "客户名称":
                            qString += string.Format(" AND FCustName LIKE '%{0}%'", textBoxCondition.Text);
                            break;
                    }
                }

                invoices = dbContext.ExecuteQuery<K3Invoice>(qString).ToList();

                if (invoices.Any())
                {
                	dataGridViewInvoice.DataSource=invoices; 
                	buttonExport.Focus();
                }
                else
                {
                    MessageBox.Show(@"无数据！");
                    dateTimePickerBegin.Focus();
                }

            }
		}
		void ButtonExportClick(object sender, EventArgs e)
		{
			if(invoices.Any())
			{
				IWorkbook hssfworkbook =new HSSFWorkbook();
			    ISheet sheet = hssfworkbook.CreateSheet("发票信息");
			    
			    K3Invoice[] k=invoices.ToArray();
			    IRow head= sheet.CreateRow(0);
			    ICell h0=head.CreateCell(0);
			    h0.SetCellValue("单据号码");
			    ICell h1=head.CreateCell(1);
			    h1.SetCellValue("购方名称");
			    ICell h2=head.CreateCell(2);
			    h2.SetCellValue("购方税号");
			    ICell h3=head.CreateCell(3);
			    h3.SetCellValue("购方地址电话");
			    ICell h4=head.CreateCell(4);
			    h4.SetCellValue("购方银行账号");
			    ICell h5=head.CreateCell(5);
			    h5.SetCellValue("备注");
			    ICell h6=head.CreateCell(6);
			    h6.SetCellValue("复核人");
			    ICell h7=head.CreateCell(7);
			    h7.SetCellValue("收款人");
			    ICell h8=head.CreateCell(8);
			    h8.SetCellValue("清单商品名称");
			    ICell h9=head.CreateCell(9);
			    h9.SetCellValue("单据日期");
			    ICell h10=head.CreateCell(10);
			    h10.SetCellValue("销方银行账号");
			    ICell h11=head.CreateCell(11);
			    h11.SetCellValue("销方地址电话");
			    ICell h12=head.CreateCell(12);
			    h12.SetCellValue("身份证校验标志");
			    ICell h13=head.CreateCell(13);
			    h13.SetCellValue("海洋石油标志");
			    ICell h14=head.CreateCell(14);
			    h14.SetCellValue("货物名称");
			    ICell h15=head.CreateCell(15);
			    h15.SetCellValue("计量单位");
			    ICell h16=head.CreateCell(16);
			    h16.SetCellValue("规格型号");
			    ICell h17=head.CreateCell(17);
			    h17.SetCellValue("数量");
			    ICell h18=head.CreateCell(18);
			    h18.SetCellValue("金额");
			    ICell h19=head.CreateCell(19);
			    h19.SetCellValue("税率");
			    ICell h20=head.CreateCell(20);
			    h20.SetCellValue("商品税目");
			    ICell h21=head.CreateCell(21);
			    h21.SetCellValue("折扣金额");
			    ICell h22=head.CreateCell(22);
			    h22.SetCellValue("税额");
			    ICell h23=head.CreateCell(23);
			    h23.SetCellValue("折扣税额");
			    ICell h24=head.CreateCell(24);
			    h24.SetCellValue("折扣率");
			    ICell h25=head.CreateCell(25);
			    h25.SetCellValue("单价");
			    ICell h26=head.CreateCell(26);
			    h26.SetCellValue("价格方式");
			    
			    for(int i=0;i<k.Count();i++)
			    {
			    	IRow row=sheet.CreateRow(i+1);

			    	ICell cell0= row.CreateCell(0);
			    	cell0.SetCellValue(k[i].FInvoiceNumber);
			    	
			    	ICell cell1= row.CreateCell(1);
			    	cell1.SetCellValue(k[i].FCustName);		

			    	ICell cell2= row.CreateCell(2);
			    	cell2.SetCellValue(k[i].FDuty);	

			    	ICell cell3= row.CreateCell(3);
			    	cell3.SetCellValue(k[i].FAddres);	

			    	ICell cell4= row.CreateCell(4);
			    	cell4.SetCellValue(k[i].FBank);	

			    	ICell cell5= row.CreateCell(5);
			    	cell5.SetCellValue(k[i].FNotes);	

			    	ICell cell6= row.CreateCell(6);
			    	cell6.SetCellValue(k[i].FChecker);	

			    	ICell cell7= row.CreateCell(7);
			    	cell7.SetCellValue(k[i].FReceiver);	

			    	ICell cell8= row.CreateCell(8);
			    	cell8.SetCellValue(k[i].FProductName);	

			    	ICell cell9= row.CreateCell(9);
			    	cell9.SetCellValue(string.Format("{0:yyyy-MM-dd}",k[i].FDate));

			    	ICell cell10= row.CreateCell(10);
			    	cell10.SetCellValue(k[i].FComBank);	

			    	ICell cell11= row.CreateCell(11);
			    	cell11.SetCellValue(k[i].FComAddress);	

			    	ICell cell12= row.CreateCell(12);
			    	cell12.SetCellValue(k[i].IdTag.ToString());

			    	ICell cell13= row.CreateCell(13);
			    	cell13.SetCellValue(k[i].SeaOilTag.ToString());

			    	ICell cell14= row.CreateCell(14);
			    	cell14.SetCellValue(k[i].FItemName);	

			    	ICell cell15= row.CreateCell(15);
			    	cell15.SetCellValue(k[i].FUnitName);	

			    	ICell cell16= row.CreateCell(16);
			    	cell16.SetCellValue(k[i].FModel);	

			    	ICell cell17= row.CreateCell(17);
			    	cell17.SetCellValue((double)k[i].FQty);

			    	ICell cell18= row.CreateCell(18);
			    	cell18.SetCellValue((double)k[i].FAmount);	

			    	ICell cell19= row.CreateCell(19);
			    	cell19.SetCellValue((double)k[i].FTaxRate);	

			    	ICell cell20= row.CreateCell(20);
			    	cell20.SetCellValue(k[i].TaxItems.ToString());

			    	ICell cell21= row.CreateCell(21);
			    	cell21.SetCellValue((double)k[i].FAmtDiscount);	

			    	ICell cell22= row.CreateCell(22);
			    	cell22.SetCellValue((double)k[i].FTaxAmount);	

			    	ICell cell23= row.CreateCell(23);
			    	cell23.SetCellValue(k[i].FDiscountTaxAmount.ToString());

			    	ICell cell24= row.CreateCell(24);
			    	cell24.SetCellValue((double)k[i].FDiscountRate);

			    	ICell cell25= row.CreateCell(25);
			    	cell25.SetCellValue((double)k[i].FPrice);
			    	
			    	ICell cell26= row.CreateCell(26);
			    	cell26.SetCellValue(k[i].PriceOffer.ToString());

			    }
			    
			    SaveFileDialog sfd = new SaveFileDialog();
			    sfd.Filter = "Excel (*.xls)|*.xls";
			    if (sfd.ShowDialog() == DialogResult.OK)
			    {
			    	FileStream file = new FileStream(sfd.FileName, FileMode.Create);
			    	hssfworkbook.Write(file);
			    	file.Close();
			    }				
			}
			else
			{
				MessageBox.Show("无数据！");
			}
			
		
	
		}
		void TextBoxConditionKeyDown(object sender, KeyEventArgs e)
		{
			if (e.KeyCode == Keys.Enter)
                ButtonQueryClick(sender, e);
		}
		void ComboBoxFiltFieldSelectedIndexChanged(object sender, EventArgs e)
		{
			if (comboBoxFiltField.SelectedItem == null || string.IsNullOrEmpty(comboBoxFiltField.SelectedItem.ToString()) || comboBoxFiltField.SelectedItem.ToString()=="无")
            {
                comboBoxFiltField.BackColor = Color.DarkGray;
            }
            else
            {
                comboBoxFiltField.BackColor = Color.Aqua;
            }	
		}
		void DateTimePickerBeginKeyDown(object sender, KeyEventArgs e)
		{
			if (e.KeyCode == Keys.Enter)
                SendKeys.Send("{Tab}");
		}
		void DateTimePickerEndKeyDown(object sender, KeyEventArgs e)
		{
			if (e.KeyCode == Keys.Enter)
                SendKeys.Send("{Tab}");
		}
		void ComboBoxFiltFieldKeyDown(object sender, KeyEventArgs e)
		{
			if (e.KeyCode == Keys.Enter)
                SendKeys.Send("{Tab}");
		}
    }
}
